package com.pssmobile.genericglossary.data;

import java.util.Vector;


import net.rim.device.api.database.*;
import net.rim.device.api.io.URI;

public class DataAdapter {

    static public DataAdapter db;
    
    static public final int SEARCH_TERMS = 1;
    static public final int SEARCH_DETAILS = 2;
    
    Database sqliteDB = null;
    
    // Hide default constructor
    private DataAdapter() {
        
    }
    
    static public DataAdapter getInstance() {
        if (null == db) {
            db = new DataAdapter();
        }
        return db;
    }
    
    public void openDatabase(String path, boolean create) throws Exception {
        URI uri = URI.create(path);
        if (!DatabaseFactory.exists(uri)) {
            if (!create) throw new Exception("Database not found"); 
            sqliteDB = DatabaseFactory.create(uri);
            _initDatabase();
        } else {
            sqliteDB = DatabaseFactory.open(uri);
        }
    }
    
    /**
     * Create initial DB structure
     */
    private void _initDatabase() throws DatabaseException {
        // Cretae tables
        Statement st = null;
        st = sqliteDB.createStatement( 
           "CREATE TABLE 'Terms' ( " +
           "'TermID' INTEGER, " +
           "'Name' TEXT, " + 
           "'Description' TEXT )" );
        st.prepare();
        st.execute();
        
        // Init with default data
        st = sqliteDB.createStatement(
                "INSERT INTO Terms(TermID, Name, Description) VALUES (?, ?, ?)" );
        st.prepare();

        st.bind(1, 1);
        st.bind(2, "Term1");
        st.bind(3, "This is a term1 description\nLine2\nThis is a very long line that dont fit into the screen width");
        st.execute();
        st.reset();
        
        st.bind(1, 2);
        st.bind(2, "Term2");
        st.bind(3, "description of term2");
        st.execute();
        st.reset();
        
        st.bind(1, 3);
        st.bind(2, "Term3");
        st.bind(3, "description of term3");
        st.execute();
        st.reset();        
        
        st.execute();
    }
    
    public void closeDatabase() throws Exception {
        try {
            sqliteDB.close();
        } finally {
            sqliteDB = null;
        }    
    }
    
    /**
     * Get terms list from DB corresponding specified criteria
     * @param criteria Only data that passed this criteria will be retrieved
     * @param scope Where to find
     * @return
     */
    public Vector findTerms( String criteria, int scope ) throws Exception {
        Vector terms = new Vector();
        
        String query = "SELECT * FROM Terms";
        String condition = "";
        if (criteria == null || criteria.equals("")) {
            // No filter
        }
        else {
            // Get filtered terms
            condition = " WHERE name like '%" + criteria + "%'" + "OR description " + "like '%" + criteria + "%'";
            query += condition;
        }

        query += " ORDER BY Name";
        
System.out.println(">>> " + query);        
        Statement st = sqliteDB.createStatement(query);        
        st.prepare();
        Cursor c = st.getCursor(); 
        
        Row r;
        while(c.next()) {
            r = c.getRow();
            int termID =  r.getInteger(0);
            terms.addElement(new Integer(termID));
        }
        
        return terms;        
    }
    
    public Term getTermById( int id ) throws Exception {
        Term term = null;
        
        Statement st = sqliteDB.createStatement("SELECT TermID,Name,Description FROM Terms WHERE TermID LIKE " + String.valueOf(id));        
        st.prepare();
        Cursor c = st.getCursor(); 
        Row r;
        if (c.next()) {
            term = new Term();
            r = c.getRow();        
            term.id = id;
            term.name = r.getString(1);
            term.descr = r.getString(2);
        }
        
        return term;
    }
}
